package com.neusoft.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.neusoft.vo.DeptHistoryVO;
import com.neusoft.vo.EmpInfoResultVO;
import com.neusoft.vo.JobHistoryVO;

public interface EmpInfoMapper {
	
	@Select("SELECT e.ename, e.email, d.dname, j.job_name, e.gender, MIN(jh.start_date) AS start_date, e.birthday, e.marriage, e.work_date\r\n"
			+ "FROM emp e JOIN job j ON e.jobno = j.jobno\r\n"
			+ " 		  JOIN dept d ON e.deptno = d.deptno\r\n"
			+ "			  RIGHT JOIN job_history jh ON e.empno = jh.empno\r\n"
			+ "WHERE e.empno = #{id}")
	public EmpInfoResultVO getEmpInfo(Integer empno);
	
	@Select("SELECT * FROM dept d JOIN emp e ON(d.deptno = e.deptno) JOIN job j ON(e.jobno = j.jobno)\r\n"
			+ "WHERE empno = #{empno}")	
	public EmpInfoResultVO getEmpLessInfo(Integer empno);
	
	@Select("SELECT start_date, end_date, dname FROM dept_history JOIN dept ON dept_history.deptno = dept.deptno\r\n"
			+ "WHERE empno = #{id} ORDER BY start_date ASC")
	public List<DeptHistoryVO> getDeptHistory(Integer empno);
	
	@Select("SELECT start_date, end_date, job_name FROM job_history JOIN job ON job_history.jobno = job.jobno\r\n"
			+ "WHERE empno = #{id} ORDER BY start_date ASC")
	public List<JobHistoryVO> getJobHistory(Integer empno);
}
